using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace FederationMembershipDB
{
	public class Address
	{
        int _ID;
        string _StreetNumber;
		string _StreetName;
		string _Suburb;
		string _State;
		string _PostCode;

        public int ID
        {
            get { return _ID; }
        }

		public void AddAddress(string streetnumber, string streetname, string suburb, string state, string postcode)
		{
			_StreetNumber = streetnumber;
            _StreetName = streetname;
            _Suburb = suburb;
            _State = state;
            _PostCode = postcode;

            //Add to database
            SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                                                         AttachDbFilename='|DataDirectory|\Federation_Database.mdf';
                                                         Integrated Security=True;User Instance=True");
            connection.Open();

            SqlCommand getNextID = new SqlCommand(string.Format("SELECT ISNULL(MAX(ID) + 1, 1) FROM Address"), connection);

            _ID = (int)(getNextID.ExecuteScalar());

            SqlCommand insertAddress= new SqlCommand(string.Format("INSERT INTO Address VALUES({0}, '{1}', '{2}', '{3}', '{4}', '{5}')",
                                                                             _ID, _StreetNumber, _StreetName, _State, _Suburb, _PostCode), connection);
            insertAddress.ExecuteNonQuery();
            connection.Close();


		}

		public string[] ViewAddress(int id)
		{
            string[] ret = new string[5];
            _ID = id;

            //retreive address from database
            SqlConnection connection = new SqlConnection(@"Data Source=.\SQLEXPRESS;
                                                         AttachDbFilename='|DataDirectory|\Federation_Database.mdf';
                                                         Integrated Security=True;User Instance=True");
            connection.Open();

            SqlCommand getdata = new SqlCommand(string.Format("SELECT a.StreetNumber, a.StreetAddress, a.Suburb, a.State, a.PostCode FROM Address a, Member m WHERE m.ID = {0} AND a.ID = m.Address_ID", _ID), connection);
            SqlDataReader dr1 = getdata.ExecuteReader();

            if (dr1.HasRows)
            {
                dr1.Read();
                for (int i = 0; i < 5; i++)
                {
                    ret[i] = (string)dr1[i];
                }

                connection.Close();
                return ret;
            }
            else
            {
                connection.Close();
                return null;
            }
		}

	}
}
